﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Windows.Forms;

namespace WinFormTool
{
    class ToolExportExcel
    {
        #region 导出excel
        /// <summary>
        /// create 2016-11-30 by sly 
        /// 将DataTable数据导入到excel中   此方法直接返回文件给浏览器下载
        /// </summary>
        /// <param name="data">要导入的数据</param>
        /// <param name="isColumnWritten">DataTable的列名是否要导入</param>
        /// <param name="sheetName">要导入的excel的sheet的名称</param>
        /// <param name="widthDic">要设置的宽度(key 列下标,val 宽度大小)</param>
        /// <returns></returns>
        public void DataTableToExcel(DataTable data, string sheetName)
        {
            SaveFileDialog saveFileDialog = new SaveFileDialog
            {
                //设置文件标题
                Title = "导出Excel文件",
                //设置文件类型
                Filter = "Excel 工作簿(*.xlsx)|*.xlsx|Excel 97-2003 工作簿(*.xls)|*.xls",
                //设置默认文件类型显示顺序  
                FilterIndex = 1,
                //是否自动在文件名中添加扩展名
                AddExtension = true,
                //是否记忆上次打开的目录
                RestoreDirectory = true,
                //设置默认文件名
                FileName = sheetName
            };
            //按下确定选择的按钮  
            if (saveFileDialog.ShowDialog() == DialogResult.OK)
            {
                //获得文件路径 
                string localFilePath = saveFileDialog.FileName.ToString();
                //NPOI
                IWorkbook workbook;
                string FileExt = Path.GetExtension(localFilePath).ToLower();
                if (FileExt == ".xlsx")
                {
                    workbook = new XSSFWorkbook();
                }
                else if (FileExt == ".xls")
                {
                    workbook = new HSSFWorkbook();
                }
                else
                {
                    workbook = null;
                }
                if (workbook == null)
                {
                    return ;
                }
                ISheet sheet = string.IsNullOrEmpty(sheetName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(sheetName);

                try
                {
                    //读取标题  
                    IRow rowHeader = sheet.CreateRow(0);
                    for (int i = 0; i < data.Columns.Count; i++)
                    {
                        ICell cell = rowHeader.CreateCell(i);
                        cell.SetCellValue(data.Columns[i].ColumnName);
                    }

                    //读取数据  
                    for (int i = 0; i < data.Rows.Count; i++)
                    {
                        IRow rowData = sheet.CreateRow(i + 1);
                        for (int j = 0; j < data.Columns.Count; j++)
                        {
                            ICell cell = rowData.CreateCell(j);
                            cell.SetCellValue(data.Rows[i][j].ToString());
                        }
                    }

                    //处理当前在消息队列中的所有 Windows 消息
                    Application.DoEvents();

                    //转为字节数组  
                    MemoryStream stream = new MemoryStream();
                    workbook.Write(stream);
                    var buf = stream.ToArray();

                    //保存为Excel文件  
                    using (FileStream fs = new FileStream(localFilePath, FileMode.Create, FileAccess.Write))
                    {
                        fs.Write(buf, 0, buf.Length);
                        fs.Flush();
                        fs.Close();
                    }

                    //成功提示
                    if (MessageBox.Show("导出成功，是否立即打开？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
                    {
                        Process.Start(localFilePath);
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                
            }
        }

        #endregion

        #region 读取Excel表格中数据到DataTable中
       
        #endregion
    }
}
